[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT structure_type_id
FROM pa_structure_types
WHERE structure_type_class_code = c_lifecycle;
SELECT 'N'
INTO l_is_uniq
FROM dual
WHERE exists(Select 'XYZ' from pa_proj_elements
WHERE element_number = P_lifecycle_short_name
AND object_type=c_object_type
AND project_id=c_project_id);
SELECT PA_TASKS_S.NEXTVAL
INTO l_lifecycle_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_elements...');
PA_PROJ_ELEMENTS_PKG.Insert_Row(
X_ROW_ID => l_row_id ,
X_PROJ_ELEMENT_ID => l_lifecycle_id ,
X_PROJECT_ID => c_project_id ,
X_OBJECT_TYPE => c_object_type ,
X_ELEMENT_NUMBER => P_lifecycle_short_name ,
X_NAME => P_lifecycle_name ,
X_DESCRIPTION => P_lifecycle_description ,
X_STATUS_CODE => NULL ,
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 => NULL ,
X_TYPE_ID => NULL ,
X_PRIORITY_CODE => NULL ,
X_INC_PROJ_PROGRESS_FLAG => NULL ,
X_REQUEST_ID => NULL ,
X_PROGRAM_APPLICATION_ID => NULL ,
X_PROGRAM_ID => NULL ,
X_PROGRAM_UPDATE_DATE => NULL ,
X_LINK_TASK_FLAG => 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_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 => NULL ,
x_phase_code => NULL ,
x_phase_version_id => NULL,
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT PA_PROJ_ELEMENT_VERSIONS_S.NEXTVAL
INTO l_pev_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_element_versions...');
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => l_row_id ,
X_ELEMENT_VERSION_ID => l_pev_id ,
X_PROJ_ELEMENT_ID => l_lifecycle_id ,
X_OBJECT_TYPE => c_object_type ,
X_PROJECT_ID => c_project_id ,
X_PARENT_STRUCTURE_VERSION_ID => NULL ,
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 => 'Working',
X_SOURCE_OBJECT_ID => c_project_id ,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT PA_PROJ_ELEM_VER_STRUCTURE_S.NEXTVAL
INTO l_pev_struct_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_elem_version_structure...');
PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
X_ROWID => l_row_id ,
X_PEV_STRUCTURE_ID => l_pev_struct_id ,
X_ELEMENT_VERSION_ID => l_pev_id ,
X_VERSION_NUMBER => 1 ,
X_NAME => P_lifecycle_name ,
X_PROJECT_ID => c_project_id ,
X_PROJ_ELEMENT_ID => l_lifecycle_id ,
X_DESCRIPTION => P_lifecycle_description ,
X_EFFECTIVE_DATE => NULL ,
X_PUBLISHED_DATE => NULL ,
X_PUBLISHED_BY => NULL ,
X_CURRENT_BASELINE_DATE => NULL ,
X_CURRENT_BASELINE_FLAG => 'Y' ,
X_CURRENT_BASELINE_BY => NULL ,
X_ORIGINAL_BASELINE_DATE => NULL ,
X_ORIGINAL_BASELINE_FLAG => 'Y' ,
X_ORIGINAL_BASELINE_BY => NULL ,
X_LOCK_STATUS_CODE => NULL ,
X_LOCKED_BY => NULL ,
X_LOCKED_DATE => NULL ,
X_STATUS_CODE => NULL ,
X_WF_STATUS_CODE => NULL ,
X_LATEST_EFF_PUBLISHED_FLAG => 'Y' ,
X_CHANGE_REASON_CODE => NULL ,
X_RECORD_VERSION_NUMBER => NULL ,
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT PA_PROJ_STRUCTURE_TYPES_S.NEXTVAL
INTO l_proj_struct_type_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_structure_types...');
PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
X_ROWID => l_row_id ,
X_PROJ_STRUCTURE_TYPE_ID => l_proj_struct_type_id ,
X_PROJ_ELEMENT_ID => l_lifecycle_id ,
X_STRUCTURE_TYPE_ID => l_structure_type_id ,
X_RECORD_VERSION_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
);
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into PA_LIFECYCLE_USAGES..');
SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
INTO l_lcyl_usage_id
FROM dual;
PA_LIFECYCLE_USAGES_PKG.INSERT_ROW(
X_LIFECYCLE_USAGE_ID => l_lcyl_usage_id ,
X_RECORD_VERSION_NUMBER => 1 ,
X_LIFECYCLE_ID => l_lifecycle_id ,
X_USAGE_TYPE => 'PROJECTS'
);
SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
INTO l_lcyl_usage_id
FROM dual;
PA_LIFECYCLE_USAGES_PKG.INSERT_ROW(
X_LIFECYCLE_USAGE_ID => l_lcyl_usage_id ,
X_RECORD_VERSION_NUMBER => 1 ,
X_LIFECYCLE_ID => l_lifecycle_id ,
X_USAGE_TYPE => 'PRODUCTS'
);
pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into PA_PROJ_ELEM_VER_SCHEDULE..');
SELECT PA_PROJ_ELEM_VER_SCHEDULE_S.NEXTVAL
INTO l_pev_sched_id
FROM dual;
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row (
X_ROW_ID => l_row_id ,
X_PEV_SCHEDULE_ID => l_pev_sched_id ,
X_ELEMENT_VERSION_ID => l_pev_id ,
X_PROJECT_ID => c_project_id ,
X_PROJ_ELEMENT_ID => l_lifecycle_id ,
X_SCHEDULED_START_DATE => sysdate ,
X_SCHEDULED_FINISH_DATE => sysdate ,
X_OBLIGATION_START_DATE => NULL ,
X_OBLIGATION_FINISH_DATE => NULL ,
X_ACTUAL_START_DATE => NULL ,
X_ACTUAL_FINISH_DATE => NULL ,
X_ESTIMATED_START_DATE => NULL ,
X_ESTIMATED_FINISH_DATE => NULL ,
X_DURATION => NULL ,
X_EARLY_START_DATE => NULL ,
X_EARLY_FINISH_DATE => NULL ,
X_LATE_START_DATE => NULL ,
X_LATE_FINISH_DATE => NULL ,
X_CALENDAR_ID => NULL ,
X_MILESTONE_FLAG => NULL ,
X_CRITICAL_FLAG => NULL ,
X_WQ_PLANNED_QUANTITY => NULL ,
X_PLANNED_EFFORT => NULL ,
X_ACTUAL_DURATION => NULL ,
X_ESTIMATED_DURATION => NULL,
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT 'N'
FROM dual
WHERE exists(Select 'XYZ'
FROM pa_proj_elements pelem
, pa_proj_element_versions phasever
WHERE phasever.PARENT_STRUCTURE_VERSION_ID = l_life_elem_ver_id
AND phasever.PROJECT_ID = c_project_id
AND phasever.OBJECT_TYPE = c_object_type
AND phasever.PROJ_ELEMENT_ID = pelem.PROJ_ELEMENT_ID
AND pelem.element_number = p_phase_short_name
AND pelem.project_id = c_project_id
AND pelem.object_type = c_object_type);
SELECT ELEMENT_VERSION_ID
FROM pa_proj_element_versions
WHERE PROJ_ELEMENT_ID = p_lifecycle_id;
SELECT PA_TASKS_S.NEXTVAL
INTO l_phase_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase:Inserting into pa_proj_elements...');
PA_PROJ_ELEMENTS_PKG.Insert_Row(
X_ROW_ID => l_row_id ,
x_proj_element_id => l_phase_id ,
x_project_id => c_project_id ,
x_object_type => c_object_type ,
x_element_number => p_phase_short_name ,
x_name => p_phase_name ,
X_DESCRIPTION => P_phase_description ,
X_STATUS_CODE => NULL ,
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 => NULL ,
X_TYPE_ID => NULL ,
X_PRIORITY_CODE => NULL ,
X_INC_PROJ_PROGRESS_FLAG => NULL ,
X_REQUEST_ID => NULL ,
X_PROGRAM_APPLICATION_ID => NULL ,
X_PROGRAM_ID => NULL ,
X_PROGRAM_UPDATE_DATE => NULL ,
X_LINK_TASK_FLAG => 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_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 => P_lifecycle_id ,
x_phase_code => p_phase_code ,
x_phase_version_id => NULL,
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT PA_PROJ_ELEMENT_VERSIONS_S.NEXTVAL
INTO l_pev_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into pa_proj_element_versions...');
PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
X_ROW_ID => l_row_id ,
X_ELEMENT_VERSION_ID => l_pev_id ,
X_PROJ_ELEMENT_ID => l_phase_id ,
X_OBJECT_TYPE => c_object_type ,
X_PROJECT_ID => c_project_id ,
X_PARENT_STRUCTURE_VERSION_ID => l_life_elem_ver_id ,
X_DISPLAY_SEQUENCE => p_phase_display_sequence ,
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 => 'Working',
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT PA_PROJ_ELEM_VER_SCHEDULE_S.NEXTVAL
INTO l_pev_sched_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into PA_PROJ_ELEMENT_VER_SCHEDULE...');
PA_PROJ_ELEMENT_SCH_PKG.Insert_Row (
X_ROW_ID => l_row_id ,
X_PEV_SCHEDULE_ID => l_pev_sched_id ,
X_ELEMENT_VERSION_ID => l_pev_id ,
X_PROJECT_ID => c_project_id ,
X_PROJ_ELEMENT_ID => l_phase_id ,
X_SCHEDULED_START_DATE => sysdate ,
X_SCHEDULED_FINISH_DATE => sysdate ,
X_OBLIGATION_START_DATE => NULL ,
X_OBLIGATION_FINISH_DATE => NULL ,
X_ACTUAL_START_DATE => NULL ,
X_ACTUAL_FINISH_DATE => NULL ,
X_ESTIMATED_START_DATE => NULL ,
X_ESTIMATED_FINISH_DATE => NULL ,
X_DURATION => NULL ,
X_EARLY_START_DATE => NULL ,
X_EARLY_FINISH_DATE => NULL ,
X_LATE_START_DATE => NULL ,
X_LATE_FINISH_DATE => NULL ,
X_CALENDAR_ID => NULL ,
X_MILESTONE_FLAG => NULL ,
X_CRITICAL_FLAG => NULL ,
X_WQ_PLANNED_QUANTITY => NULL ,
X_PLANNED_EFFORT => NULL ,
X_ACTUAL_DURATION => NULL ,
X_ESTIMATED_DURATION => NULL,
X_SOURCE_OBJECT_ID => c_project_id,
X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
);
SELECT pa_object_relationships_s.NEXTVAL
INTO l_obj_relnship_id
FROM dual;
pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into PA_OBJECT_RELATIONSHIPS...');
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => FND_GLOBAL.USER_ID ,
p_object_type_from => 'PA_STRUCTURES' ,
p_object_id_from1 => l_life_elem_ver_id ,
p_object_id_from2 => NULL ,
p_object_id_from3 => NULL ,
p_object_id_from4 => NULL ,
p_object_id_from5 => NULL ,
p_object_type_to => 'PA_TASKS' ,
p_object_id_to1 => l_pev_id ,
p_object_id_to2 => NULL ,
p_object_id_to3 => NULL ,
p_object_id_to4 => NULL ,
p_object_id_to5 => NULL ,
p_relationship_type => 'S' ,
p_relationship_subtype => 'STRUCTURE_TO_TASK' ,
p_lag_day => NULL ,
p_imported_lag => NULL ,
p_priority => NULL ,
p_pm_product_code => NULL ,
x_object_relationship_id => l_obj_relnship_id ,
x_return_status => l_return_status
);
PROCEDURE delete_lifecycle(
P_api_version IN NUMBER :=1.0 ,
P_commit IN VARCHAR2 :=FND_API.G_FALSE ,
P_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
P_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
P_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
P_debug_mode IN VARCHAR2 :='N' ,
P_max_msg_count IN NUMBER :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_record_version_number IN NUMBER ,
X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle';
SELECT rowid,element_version_id
FROM pa_proj_element_versions
WHERE proj_element_id = P_lifecycle_id
AND project_id = c_project_id
AND object_type = c_object_type;
SELECT rowid
FROM pa_proj_elem_ver_schedule
WHERE proj_element_id = P_lifecycle_id
AND element_version_id = l_element_version_id
AND project_id = c_project_id;
SELECT rowid,record_version_number
FROM pa_proj_workplan_attr
WHERE proj_element_id = P_lifecycle_id
AND project_id = c_project_id;
SELECT rowid
FROM pa_proj_elem_ver_structure
WHERE element_version_id = l_element_version_id
AND proj_element_id = P_lifecycle_id
AND project_id = c_project_id;
SELECT rowid
FROM pa_proj_structure_types
WHERE proj_element_id = P_lifecycle_id;
SELECT LIFECYCLE_USAGE_ID
FROM pa_lifecycle_usages
WHERE lifecycle_id = P_lifecycle_id;
pa_debug.debug('CREATE_LIFECYCLE_PVT: inside delete_lifecycle.....');
pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phase: Checking for call compatability...');
pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phase: Locking on pa_proj_elements...');
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = P_lifecycle_id
AND record_version_number = p_record_version_number
AND project_id = c_project_id
AND object_type = c_object_type
FOR update of record_version_number NOWAIT;
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = P_lifecycle_id
AND project_id = c_project_id
AND object_type = c_object_type
AND record_version_number = p_record_version_number;
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle: checking message count');
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENTS_PKG.delete_Row');
PA_PROJ_ELEMENTS_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row');
PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENT_SCH_PKG.delete_Row');
PA_PROJ_ELEMENT_SCH_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_Row');
PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_STRUCTURE_TYPES_PKG.delete_Row');
PA_PROJ_STRUCTURE_TYPES_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_LIFECYCLE_USAGES_PKG.delete_Row');
PA_LIFECYCLE_USAGES_PKG.Delete_Row(l_LIFECYCLE_USAGE_ID);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle: checking message count');
p_procedure_name => 'DELETE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'DELETE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_lifecycle;
PROCEDURE delete_lifecycle_phase (
P_api_version IN NUMBER := 1.0 ,
P_commit IN VARCHAR2 := FND_API.G_FALSE ,
P_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
P_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
P_debug_mode IN VARCHAR2 := 'N' ,
P_max_msg_count IN NUMBER := G_MISS_NUM ,
P_phase_id IN NUMBER ,
p_record_version_number IN NUMBER ,
X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
) IS
l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle_phase';
Select rowid,element_version_id,parent_structure_version_id
From pa_proj_element_versions
Where proj_element_id = P_phase_id
AND project_id = c_project_id
AND object_type = c_object_type;
Select rowid
From pa_proj_elem_ver_schedule
Where proj_element_id = P_phase_id
AND element_version_id = l_element_version_id
AND project_id = c_project_id;
Select object_relationship_id, record_version_number
From pa_object_relationships
Where object_type_from = 'PA_STRUCTURES'
and object_id_from1 = l_par_element_version_id
and object_type_to = c_object_type
and object_id_to1 = l_element_version_id
and relationship_subtype = 'STRUCTURE_TO_TASK';
pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phases:inside delete_lifecycle_phase..');
pa_debug.debug('PA_LIFECYCLES_PVT.delete_lifecycle_phase:Locking record for pa_proj_elements...');
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = p_phase_id
AND project_id = c_project_id
AND object_type = c_object_type
AND record_version_number = p_record_version_number
FOR update of record_version_number NOWAIT;
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = p_phase_id
AND project_id = c_project_id
AND object_type = c_object_type
AND record_version_number = p_record_version_number;
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases: checking message count');
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENTS_PKG.delete_Row');
PA_PROJ_ELEMENTS_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row');
PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENT_SCH_PKG.delete_Row');
PA_PROJ_ELEMENT_SCH_PKG.delete_Row(l_rowid);
pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_OBJECT_RELATIONSHIPS_PKG.delete_Row');
PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
p_object_relationship_id => l_obj_rel_id ,
p_object_type_from => NULL ,
p_object_id_from1 => NULL ,
p_object_id_from2 => NULL ,
p_object_id_from3 => NULL ,
p_object_id_from4 => NULL ,
p_object_id_from5 => NULL ,
p_object_type_to => NULL ,
p_object_id_to1 => NULL ,
p_object_id_to2 => NULL ,
p_object_id_to3 => NULL ,
p_object_id_to4 => NULL ,
p_object_id_to5 => NULL ,
p_record_version_number => l_record_version_number ,
p_pm_product_code => NULL ,
x_return_status => l_return_status
);
pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phases: checking message count');
p_procedure_name => 'delete_lifecycle_phase',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'delete_lifecycle_phase',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_lifecycle_phase;
PROCEDURE check_delete_lifecycle_ok(
P_api_version IN NUMBER :=1.0 ,
P_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
P_debug_mode IN VARCHAR2 :='N' ,
P_max_msg_count IN NUMBER :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_version_id IN NUMBER ,
X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_del_lifecycle_ok OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_api_name CONSTANT VARCHAR(30) := 'check_delete_lifecycle_ok';
Select 'Y'
From dual
Where exists(Select 'XYZ'
From pa_lifecycle_usages
Where lifecycle_id = P_lifecycle_id
and usage_type = c_usage_project);
Select 'Y'
From dual
Where exists(Select 'XYZ'
From pa_lifecycle_usages
Where lifecycle_id = P_lifecycle_id
and usage_type = c_usage_product);
Select 'Y'
From dual
Where exists(Select 'XYZ'
From pa_proj_workplan_attr
Where lifecycle_version_id = P_lifecycle_version_id);
pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Inside check_delete_lifecycle_ok...');
pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Checking for valid parameters..');
pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Checking for assigned to workplan..');
pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: checking message count');
p_procedure_name => 'delete_lifecycle_phase_ok',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'delete_lifecycle_phase_ok',
p_error_text => SUBSTRB(SQLERRM,1,240));
END check_delete_lifecycle_ok;
PROCEDURE update_lifecycle (
p_api_version IN NUMBER :=1.0 ,
p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
p_debug_mode IN VARCHAR2 :='N' ,
p_max_msg_count IN NUMBER :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_short_name IN VARCHAR2 ,
P_lifecycle_name IN VARCHAR2 ,
P_lifecycle_description IN VARCHAR2 ,
P_lifecycle_project_usage_type IN VARCHAR2 ,
P_lifecycle_product_usage_type IN VARCHAR2 ,
P_record_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_lifecycle';
SELECT element_version_id
FROM pa_proj_element_versions
WHERE proj_element_id = P_lifecycle_id
AND project_id = c_project_id
AND object_type = c_object_type;
SELECT lifecycle_usage_id
from pa_lifecycle_usages
where lifecycle_id = P_lifecycle_id
AND usage_type=c_project_type;
SELECT lifecycle_usage_id
from pa_lifecycle_usages
where lifecycle_id = P_lifecycle_id
AND usage_type=c_product_type;
Select rowid,PEV_STRUCTURE_ID,element_version_id,record_version_number
From pa_proj_elem_ver_structure
WHERE element_version_id = l_element_version_id
AND proj_element_id = P_lifecycle_id
AND project_id = c_project_id;
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Inside update_lifecycle...');
SAVEPOINT LCYL_UPDATE_LIFECYCLE_PVT;
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking api compatibility...');
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking for used by workplan structure or not..');
SELECT 'Y'
into l_workplan_used
from SYS.DUAL
where exists(select 'XYZ' from pa_proj_workplan_attr
where lifecycle_version_id = l_element_version_id);
PA_EGO_WRAPPER_PUB.check_delete_lifecycle_ok(
p_api_version => P_api_version ,
p_lifecycle_id => p_lifecycle_id ,
x_delete_ok => l_item_used ,
x_return_status => l_return_status ,
x_errorcode => l_errorcode ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: checking message count');
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: After checking message count and applying locking ');
select rowid into l_rowid
from pa_proj_elements
where proj_element_id = p_lifecycle_id
AND record_version_number = p_record_version_number
AND project_id = c_project_id
AND object_type = c_object_type
for update of record_version_number NOWAIT;
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = P_lifecycle_id
AND project_id = c_project_id
AND object_type = c_object_type
AND record_version_number = p_record_version_number;
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into pa_proj_elements...');
PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
X_ROW_ID => l_rowid ,
X_PROJ_ELEMENT_ID => P_lifecycle_id ,
X_PROJECT_ID =>c_project_id ,
X_OBJECT_TYPE =>c_object_type ,
X_ELEMENT_NUMBER =>P_lifecycle_short_name ,
X_NAME =>P_lifecycle_name ,
X_DESCRIPTION =>P_lifecycle_description ,
X_STATUS_CODE =>NULL ,
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 => NULL ,
X_TYPE_ID => NULL ,
X_PRIORITY_CODE => NULL ,
X_INC_PROJ_PROGRESS_FLAG => NULL ,
X_RECORD_VERSION_NUMBER => P_record_version_number ,
X_REQUEST_ID => NULL ,
X_PROGRAM_APPLICATION_ID => NULL ,
X_PROGRAM_ID => NULL ,
X_PROGRAM_UPDATE_DATE => 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_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 => NULL ,
X_phase_code => NULL ,
X_phase_version_id => NULL
);
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into PA_PROJ_ELEM_VER_STRUCTURE...');
PA_PROJ_ELEM_VER_STRUCTURE_PKG.UPDATE_ROW (
X_ROWID =>l_rowid ,
X_PEV_STRUCTURE_ID =>l_pev_id ,
X_ELEMENT_VERSION_ID =>l_elem_vers_id ,
X_VERSION_NUMBER => 1 ,
X_NAME => P_lifecycle_name ,
X_PROJECT_ID => c_project_id ,
X_PROJ_ELEMENT_ID => P_lifecycle_id ,
X_DESCRIPTION => P_lifecycle_description ,
X_EFFECTIVE_DATE => NULL ,
X_PUBLISHED_DATE => NULL ,
X_PUBLISHED_BY => NULL ,
X_CURRENT_BASELINE_DATE => NULL ,
X_CURRENT_BASELINE_FLAG => 'Y' ,
X_CURRENT_BASELINE_BY => NULL ,
X_ORIGINAL_BASELINE_DATE => NULL ,
X_ORIGINAL_BASELINE_FLAG => 'Y' ,
X_ORIGINAL_BASELINE_BY => NULL ,
X_LOCK_STATUS_CODE => NULL ,
X_LOCKED_BY => NULL ,
X_LOCKED_DATE => NULL ,
X_STATUS_CODE => NULL ,
X_WF_STATUS_CODE => NULL ,
X_LATEST_EFF_PUBLISHED_FLAG => 'Y' ,
X_CHANGE_REASON_CODE => NULL ,
X_RECORD_VERSION_NUMBER => l_record_version_number
);
pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into PA_LIFECYCLE_USAGES...');
SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
INTO l_prj_lcyl_usage_id
FROM dual;
PA_LIFECYCLE_USAGES_PKG.insert_row(
X_lifecycle_usage_id => l_prj_lcyl_usage_id ,
X_RECORD_VERSION_NUMBER => 1 ,
X_lifecycle_ID => P_lifecycle_id ,
X_USAGE_TYPE => c_project_type
);
PA_LIFECYCLE_USAGES_PKG.delete_Row(l_prj_lcyl_usage_id);
SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
INTO l_prd_lcyl_usage_id
FROM dual;
PA_LIFECYCLE_USAGES_PKG.insert_row(
X_lifecycle_usage_id => l_prd_lcyl_usage_id ,
X_RECORD_VERSION_NUMBER => 1 ,
X_lifecycle_ID => P_lifecycle_id ,
X_USAGE_TYPE => c_product_type
);
PA_LIFECYCLE_USAGES_PKG.delete_Row(l_prd_lcyl_usage_id);
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
p_procedure_name => 'UPDATE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
p_procedure_name => 'UPDATE_LIFECYCLE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_lifecycle;
PROCEDURE update_lifecycle_phase (
P_api_version IN NUMBER :=1.0 ,
p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
p_debug_mode IN VARCHAR2 :='N' ,
P_max_msg_count IN NUMBER :=G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_phase_id IN NUMBER ,
P_phase_display_sequence IN NUMBER ,
P_phase_code IN VARCHAR2 ,
P_phase_short_name IN VARCHAR2 ,
P_phase_name IN VARCHAR2 ,
P_phase_description IN VARCHAR2 ,
P_record_version_number IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)IS
l_api_name CONSTANT VARCHAR2(30) := 'update_lifecycle_phase';
l_update_ok VARCHAR2(1);
Select rowid,PEV_STRUCTURE_ID,element_version_id
From pa_proj_elem_ver_structure
Where proj_element_id = P_lifecycle_id
AND project_id = c_project_id;
Select rowid,element_version_id,parent_structure_version_id,record_version_number
From pa_proj_element_versions
Where proj_element_id = P_lifecycle_phase_id
AND project_id = c_project_id
AND object_type = c_object_type;
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Inside update_lifecycle_phase...');
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking api compatibility...');
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Locking record ..');
select rowid into l_rowid
from pa_proj_elements
where proj_element_id = p_lifecycle_phase_id
AND record_version_number = p_record_version_number
AND project_id = c_project_id
AND object_type = c_object_type
for update of record_version_number NOWAIT;
SELECT rowid into l_rowid
FROM pa_proj_elements
WHERE proj_element_id = p_lifecycle_phase_id
AND project_id = c_project_id
AND object_type = c_object_type
AND record_version_number = p_record_version_number;
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking message count...');
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Updating PA_PROJ_ELEMENTS...');
PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
X_ROW_ID => l_rowid ,
X_PROJ_ELEMENT_ID => P_lifecycle_phase_id ,
X_PROJECT_ID =>c_project_id ,
X_OBJECT_TYPE =>c_object_type ,
X_ELEMENT_NUMBER =>P_phase_short_name ,
X_NAME =>P_phase_name ,
X_DESCRIPTION =>P_phase_description ,
X_STATUS_CODE => NULL ,
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 => NULL ,
X_TYPE_ID => NULL ,
X_PRIORITY_CODE => NULL ,
X_INC_PROJ_PROGRESS_FLAG => NULL ,
X_RECORD_VERSION_NUMBER => P_record_version_number ,
X_REQUEST_ID => NULL ,
X_PROGRAM_APPLICATION_ID => NULL ,
X_PROGRAM_ID => NULL ,
X_PROGRAM_UPDATE_DATE => 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_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 => P_lifecycle_id ,
x_phase_code => p_phase_code ,
x_phase_version_id => NULL
);
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Updating PA_PROJ_ELEMENT_VERSIONS...');
PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
X_ROW_ID => l_rowid ,
X_ELEMENT_VERSION_ID =>l_elem_vers_id ,
X_PROJ_ELEMENT_ID =>P_lifecycle_phase_id ,
X_OBJECT_TYPE => c_object_type ,
X_PROJECT_ID => c_project_id ,
X_PARENT_STRUCTURE_VERSION_ID =>l_parent_elem_vers_id ,
X_DISPLAY_SEQUENCE =>p_phase_display_sequence ,
X_WBS_LEVEL => NULL ,
X_WBS_NUMBER => NULL ,
X_RECORD_VERSION_NUMBER => l_record_version_number ,
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 => 'Working'
);
pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking message count after Updating tables.');
p_procedure_name => 'update_lifecycle_phase' ,
p_error_text => SUBSTRB(SQLERRM,1,240)
);
p_procedure_name => 'update_lifecycle_phase' ,
p_error_text => SUBSTRB(SQLERRM,1,240));
END update_lifecycle_phase;
PROCEDURE check_delete_lcyl_phase_ok(
P_api_version IN NUMBER := 1.0 ,
p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
p_debug_mode IN VARCHAR2 := 'N' ,
P_max_msg_count IN NUMBER := G_MISS_NUM ,
P_lifecycle_id IN NUMBER ,
P_lifecycle_phase_id IN NUMBER ,
x_delete_ok OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'check_delete_lcyl_phase_ok';
l_delete_ok VARCHAR2(1) := FND_API.G_TRUE;
pa_debug.debug('PA_LIFECYCLE PVT.check_delete_lcyl_phase_ok: Checking api compatibility...');
select 'Y'
into l_project_usage_exists
from SYS.DUAL
where exists( select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_project_type);
select 'Y'
into l_product_usage_exists
from SYS.DUAL
where exists( select 'XYZ' from pa_lifecycle_usages
where lifecycle_id=P_lifecycle_id AND
usage_type=c_product_type);
select element_version_id
into l_parent_elem_ver_id
from pa_proj_element_versions
where proj_element_id = P_lifecycle_id
and object_type = 'PA_STRUCTURES'
and project_id = c_project_id;
select element_version_id
into l_child_elem_ver_id
from pa_proj_element_versions
where proj_element_id = P_lifecycle_phase_id
and object_type = 'PA_TASKS'
and project_id = c_project_id;
then we cannot allow update of sequence number and phase code
*/
IF(p_debug_mode = 'Y') THEN
pa_debug.debug('PA_LIFECYCLE PVT.check_delete_lcyl_phase_ok: After checking for usuage type checking for top task assigned');
select FND_API.G_FALSE into l_delete_ok
from sys.dual
where exists(
select 'xyz'
from pa_proj_workplan_attr
where lifecycle_version_id = l_parent_elem_ver_id
and current_phase_version_id = l_child_elem_ver_id);
l_delete_ok := FND_API.G_TRUE;
IF l_delete_ok <> FND_API.G_TRUE THEN
PA_UTILS.ADD_MESSAGE(
p_app_short_name => 'PA',
p_msg_name => 'PA_LCYL_USED_CURR_PHASE');
x_delete_ok := l_delete_ok;
select FND_API.G_FALSE into l_delete_ok
from sys.dual
where exists(
select 'xyz'
from pa_proj_element_versions child
,pa_proj_elements tasks
where child.parent_structure_version_id = l_parent_elem_ver_id
and child.project_id = c_project_id
and child.object_type = 'PA_TASKS'
and tasks.phase_version_id = child.element_version_id
and tasks.project_id <> 0
and tasks.object_type = 'PA_TASKS');
l_delete_ok := FND_API.G_TRUE;
IF l_delete_ok <> FND_API.G_TRUE THEN
PA_UTILS.ADD_MESSAGE(
p_app_short_name => 'PA',
p_msg_name => 'PA_LCYL_PHASE_TASK_USED');
x_delete_ok := l_delete_ok;
pa_debug.debug('PA_LIFECYCLES_PVT.check_delete_lcyl_phase_ok: checking message count ');
p_procedure_name => 'check_delete_lcyl_phase_ok',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'check_delete_lcyl_phase_ok',
p_error_text => SUBSTRB(SQLERRM,1,240));
END check_delete_lcyl_phase_ok;